学习MySQL： 获取元数据

作者：royalwzy 海通证券股份有限公司 数据库架构师

元数据访问方法

1.什么是元数据:

数据库是数据的结构化集合,元数据是“有关数据的数据”;

2.MySQL通过以访问元数据的方法:

（1）INFORMATION_SCHEMA:MySQL服务器包含一个被实现为名为INFORMATION_SCHEMA的数据库(模式)的数据字典,其中包含许多显示为表的对象;

（2）SHOW语句:用于获取服务器统计信息,模式和模式对象的相关数据的专用语法;

- SHOW DATABASES和SHOW TABLES:返回包含数据库和表名的列表;

- SHOW COLUMNS:生成表中列的定义;SHOW COLUMNS FROM table等于DESC table;

- 需要有SELECT特权才能使用SHOW语句;

（3）DESCRIBE:可用于检查表结构和列属性的SQL语句快捷方式;

（4）mysqlshow:用作指向一些SHOW语句的命令行前端的客户机程序;

INFORMATION_SCHEMA数据库

INFORMATION_SCHEMA表;

1.表信息

• COLUMNS:表和视图中的列

• ENGINES:存储引擎

• SCHEMATA:数据库

• TABLES:数据库中的表

• VIEWS:数据库中的视图

2.分区

• PARTITIONS:表分区

• FILES:存储 MySQL NDB 磁盘数据表的文件

3.特权

• COLUMN_PRIVILEGES:MySQL 用户帐户所拥有的列特权

• SCHEMA_PRIVILEGES:MySQL 用户帐户所拥有的数据库特权

• TABLE_PRIVILEGES:MySQL 用户帐户所拥有的表特权

• USER_PRIVILEGES:MySQL 用户帐户所拥有的全局特权

4.字符集支持

• CHARACTER_SETS:可用的字符集

• COLLATIONS:每个字符集的排序

• COLLATION_CHARACTER_SET_APPLICABILITY:适用于特定字符集的排序

5.约束和索引

• KEY_COLUMN_USAGE:关键列的约束

• REFERENTIAL_CONSTRAINTS:外键

• STATISTICS:表索引

• TABLE_CONSTRAINTS:表的约束

6.服务器设置和状态

• KEY_COLUMN_USAGE:约束

• GLOBAL_STATUS:所有 MySQL 连接的状态值

• GLOBAL_VARIABLES:用于新的 MySQL 连接的值

• PLUGINS:服务器插件

• PROCESSLIST:指示哪些线程正在运行

• SESSION_STATUS:当前 MySQL 连接的状态值

• SESSION_VARIABLES:当前 MySQL 连接的生效值

7.例程及相关信息

• EVENTS:预定事件

• ROUTINES:存储过程和功能

• TRIGGERS:数据库中的触发器

• PARAMETERS:存储过程和功能参数以及存储函数

8.InnoDB

• INNODB_CMP 和 INNODB_CMP_RESET:对压缩的 InnoDB 表的相关操作的状态

• INNODB_CMPMEM 和 INNODB_CMPMEM_RESET:InnoDB 缓冲池中压缩页面的状态

• INNODB_LOCKS:InnoDB 事务所请求和持有的每个锁

• INNODB_LOCK_WAITS:每个阻塞的 InnoDB 事务的一个或多个行锁

• INNODB_TRX:当前正在 InnoDB 内部执行的所有事务

• TABLESPACES:活动的表空间

INFORMATION_SCHEMA表列;

对INFORMATION_SCHEMA使用SELECT;

INFORMATION_SCHEMA示例;

1.显示用于给定数据库中表的存储引擎;

2.查找所有包含SET列的表;

3.显示每个字符集的默认排序规则;

4.显示每个数据库中表的编号;

5.INFORMATION_SCHEMA表是只读的,无法用INSERT/DELETE/UPDATE之类的语句进行修改;如果执行这些类型的语句以尝试更改INFORMATION_SCHEMA表中的数据,服务器将生成错误;

使用INFORMATION_SCHEMA表创建Shell命令;

1.SQL语句将生成一条输出,仅导出world_innodb数据库中那些以单词“Country”开始的的表;

2.输出将生成可以在shell命令行上正确执行的shell脚本;下一步是将此输出存储在一个可在shell命令行中执行的批处理文件中,这通过添加子句INTO OUTFILE来完成:

 SELECT CONCAT(“mysqldump -uroot -pmysql “, TABLE_SCHEMA, ” “, TABLE_NAME, ” >> “,TABLE_SCHEMA, “.sql”)

  FROM TABLES WHERE TABLE_NAME LIKE ‘Country%’

  INTO OUTFILE ‘\tmp\Country_Dump.sh’

3.然后可以在命令行中执行此文件,命令行将运行本幻灯片中所示的两个mysqldump命令:

shell> \tmp\Country_Dump.sh

shell> \tmp\mysqldump -uroot -pmysql world_innodb Country >> world_innodb.sql

shell> \tmp\mysqldump -uroot -pmysql world_innodb Country_Language >> world_innodb.sql

使用INFORMATION_SCHEMA表创建SQL语句;

1.本幻灯片中的示例使用mysql命令执行了一个语句,以制作world_innodb数据库中所有表的精确副本;

（1）–silent命令在输出中删除列标题;

（2）–skip-column-names命令删除输出中的格式(使输出类似于表的格式);

（3）这两个命令用来确保对命令自身的解释是正确的,没有任何干扰执行的外部格式或标题行问题;

2.添加管道符号[|]并随之执行mysql命令会将这些SQL语句发送到MySQL服务器以便执行:

shell> mysql -uroot -pmysql –silent –skip-column-names -e “SELECT CONCAT(‘CREATE TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘_backup LIKE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘world_innodb’;” | mysql -uroot -pmysql

MySQL支持的 SHOW 语句;

1.除了INFORMATION_SCHEMA表之外,MySQL还支持SHOW和DESCRIBE语句,作为访问元数据的备选方式;

2.SHOW和DESCRIBE语法不如使用INFORMATION_SCHEMA查询灵活,但是对于大多数用途,SHOW和DESCRIBE语法就足够了;在这些情况下,使用MySQL特定语法通常会更快速,简单;

3.可以通过多种形式使用SHOW语句,如下所示:

- SHOW DATABASES:列出可用数据库的名称

- SHOW TABLES:列出默认数据库中的表

- SHOW TABLES FROM <database_name>:列出指定数据库中的表

- SHOW COLUMNS FROM <table_name>:显示表的列结构

- SHOW INDEX FROM <table_name>:显示表中有关索引和索引列的信息

- SHOW CHARACTER SET:显示可用的字符集及其默认排序

- SHOW COLLATION:显示每个字符集的排序

SHOW语句示例;

mysql> SHOW DATABASES;
mysql> SHOW TABLES;
mysql> SHOW TABLES FROM mysql;
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
mysql> SHOW COLUMNS FROM CountryLanguage;
mysql> SHOW FULL COLUMNS FROM CountryLanguage\G

DESCRIBE语句;

1.DESC table_name等效于SHOW COLUMNS FROM table_name;但是,SHOW COLUMNS支持可选的LIKE和WHERE子句,而DESCRIBE不支持;

2.当指定表名称作为参数时,EXPLAIN等效于DESCRIBE:mysql> EXPLAIN table_name;

mysqlshow客户机;

1.mysqlshow客户机为各种格式的SHOW语句提供了一个命令行界面,这些语句用于列出数据库的名称,数据库中的表或有关表列或索引的信息;

2.mysqlshow客户机的选项部分可包含任一标准连接参数选项,例如–host或–user;如果默认连接参数不适合,则必须提供选项;mysqlshow也接受特定于其自身运行的选项;

3.使用–help选项调用mysqlshow可查看其选项的完整列表;

4.mysqlshow所执行的操作取决于已提供的非选项参数的数量;

mysqlshow示例;

1.在没有参数的情况下,mysqlshow将显示类似于SHOW DATABASES的结果;

2.在使用单个参数的情况下,mysqlshow将该参数解释为数据库名称,并针对该数据库显示类似于SHOW TABLES的结果;

3.在有两个参数的情况下,mysqlshow将参数解释为数据库和表名称,并针对该表显示类似于SHOW FULL COLUMNS的结果;

4.在有三个参数的情况下,其输出与两个参数的情况相同,不同之处在于:mysqlshow将第三个参数当做列名称,且仅针对该列显示SHOW FULL COLUMNS输出;

5.如果命令行中最后的参数包含特殊字符,mysqlshow会将该参数解释为模式,且仅显示与该模式匹配的名称;特殊字符包括:%或*(匹配任一字符序列),以及_或?(匹配任一单个字符);本示例中的命令仅显示那些名称始于w的数据库

长按下图二维码关注“AIX专家俱乐部”公众号